package com.sduonline.isdapp.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.sduonline.isdapp.model.Article;
import com.sduonline.isdapp.model.temp.ArticleBrief;
import com.sduonline.isdapp.model.temp.ArticleDetail;

/**
 * 对Article操作的dao，使用时必须先初始化（new）。 使用连接池。
 * 
 * @author 沧
 * 
 */
public class ArticleDao {

    private static ConnectionPool pool;

    public ArticleDao() {
    	if (pool == null) {
			pool = ConnectionPool.getInstance();
		}
	}

	/**
	 * 以对象形式保存文章
	 * 
	 * @param article
	 * @return
	 */
	public boolean save(Article article) {
		Connection conn = pool.getConnection();
		String sql = "insert into isd_article(web,category,title,content,time,baseUrl,imgs,atype) values(?,?,?,?,?,?,?,?)";
		PreparedStatement ptmt = null;

		try {
			conn.setAutoCommit(false);

			ptmt = conn.prepareStatement(sql);

			ptmt.setInt(1, article.getWeb());
			ptmt.setInt(2, article.getCategory());
			ptmt.setString(3, article.getTitle());
			ptmt.setString(4, article.getContent());
			ptmt.setString(5, article.getTime());
			ptmt.setString(6, article.getBaseUrl());
			ptmt.setString(7, article.getImgs());
			ptmt.setInt(8, article.getAtype());

			ptmt.execute();
			conn.commit();
			ptmt.close();
			conn.close();

			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
			return false;
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 以对象形式update文章
	 * 
	 * @param article
	 * @return
	 */
	public boolean update(Article article) {
		Connection conn = pool.getConnection();
		String sql = "update article set web=?,category=?,title=?,content=?,time=?,baseUrl=?,imgs=? ,atype=? where id = ?";
		PreparedStatement ptmt = null;

		try {
			conn.setAutoCommit(false);

			ptmt = conn.prepareStatement(sql);

			ptmt.setInt(1, article.getWeb());
			ptmt.setInt(2, article.getCategory());
			ptmt.setString(3, article.getTitle());
			ptmt.setString(4, article.getContent());
			ptmt.setString(5, article.getTime());
			ptmt.setString(6, article.getBaseUrl());
			ptmt.setString(7, article.getImgs());
			ptmt.setInt(8, article.getAtype());
			ptmt.setInt(9, article.getId());
			ptmt.execute();

			ptmt.close();
			conn.commit();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
			return false;
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * 批量保存文章
	 * 
	 * @param articles
	 * @return
	 */
	public boolean saveBatch(List<Article> articles) {
		Connection conn = pool.getConnection();
		String sql = "insert into isd_article(web,category,title,content,time,baseUrl,imgs,atype) values(?,?,?,?,?,?,?,?)";
		PreparedStatement ptmt = null;

		try {
			conn.setAutoCommit(false);
			ptmt = conn.prepareStatement(sql);

			for (Article article : articles) {
				ptmt.setInt(1, article.getWeb());
				ptmt.setInt(2, article.getCategory());
				ptmt.setString(3, article.getTitle());
				ptmt.setString(4, article.getContent());
				ptmt.setString(5, article.getTime());
				ptmt.setString(6, article.getBaseUrl());
				ptmt.setString(7, article.getImgs());
				ptmt.setInt(8, article.getAtype());
				ptmt.addBatch();
			}

			ptmt.executeBatch();
			ptmt.close();
			conn.commit();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
			return false;
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * 批量保存文章，并且已经保存过的文章不再存入数据库。（以title判别）
	 * 
	 * @param articles
	 * @return
	 */
	public boolean saveOrUpdateBatch(List<Article> articles) {
		Connection conn = pool.getConnection();
		String sql = "insert into isd_article(web,category,title,content,time,baseUrl,imgs,atype) values(?,?,?,?,?,?,?,?) on duplicate key update web=?,category=?,content=?,time=?,baseUrl=?,imgs=?,atype=? ";
		PreparedStatement ptmt = null;

		try {
			conn.setAutoCommit(false);

			ptmt = conn.prepareStatement(sql);

			for (Article article : articles) {
				ptmt.setInt(1, article.getWeb());
				ptmt.setInt(2, article.getCategory());
				ptmt.setString(3, article.getTitle());
				ptmt.setString(4, article.getContent());
				ptmt.setString(5, article.getTime());
				ptmt.setString(6, article.getBaseUrl());
				ptmt.setString(7, article.getImgs());
				ptmt.setInt(8, article.getAtype());
				ptmt.setInt(9, article.getWeb());
				ptmt.setInt(10, article.getCategory());
				ptmt.setString(11, article.getContent());
				ptmt.setString(12, article.getTime());
				ptmt.setString(13, article.getBaseUrl());
				ptmt.setString(14, article.getImgs());
				ptmt.setInt(15, article.getAtype());
				ptmt.addBatch();
			/*	System.out.println();
				System.out.println();
				System.out.println();
				System.out.println();*/

				System.out.print("atype");

				System.out.println(article.getAtype());

			}
			ptmt.executeBatch();
			conn.commit();
			ptmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
			return false;
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * 删除文章
	 * 
	 * @param articleId
	 * @return
	 */
	public boolean delete(int articleId) {
		Connection conn = pool.getConnection();
		String sql = "delete from isd_article where id = ?";
		PreparedStatement ptmt = null;
		try {
			conn.setAutoCommit(false);

			ptmt = conn.prepareStatement(sql);
			ptmt.setInt(1, articleId);
			ptmt.execute();
			conn.commit();

			ptmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
			return false;
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 以id获取文章对象
	 * 
	 * @param articleId
	 * @return
	 */
	public Article getById(int articleId) {
		Connection conn = pool.getConnection();
		String sql = "select * from isd_article where id = ?";
		PreparedStatement ptmt = null;

		try {
			ptmt = conn.prepareStatement(sql);
			ptmt.setInt(1, articleId);
			ResultSet rs = ptmt.executeQuery();

			Article article = new Article();
			article.setId(rs.getInt("id"));
			article.setWeb(rs.getInt("web"));
			article.setCategory(rs.getInt("category"));
			article.setTitle(rs.getString("title"));
			article.setContent(rs.getString("content"));
			article.setTime(rs.getString("time"));
			article.setBaseUrl(rs.getString("baseUrl"));
			article.setBaseUrl(rs.getString("imgs"));

			rs.close();
			ptmt.close();
			conn.close();

			return article;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * 以title获取文章对象
	 * 
	 * @param title
	 * @return
	 */
	public Article getByTitle(String title) {
		Connection conn = pool.getConnection();
		String sql = "select * from isd_article where title = ?";
		PreparedStatement ptmt = null;

		try {
			ptmt = conn.prepareStatement(sql);
			ptmt.setString(1, title);
			ResultSet rs = ptmt.executeQuery();

			Article article = new Article();
			article.setId(rs.getInt("id"));
			article.setWeb(rs.getInt("web"));
			article.setCategory(rs.getInt("category"));
			article.setTitle(rs.getString("title"));
			article.setContent(rs.getString("content"));
			article.setTime(rs.getString("time"));
			article.setBaseUrl(rs.getString("baseUrl"));
			article.setBaseUrl(rs.getString("imgs"));

			rs.close();
			ptmt.close();
			conn.close();

			return article;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * 得到article的详情，该id没有文章则返回null
	 * 
	 * @param id
	 * @return
	 */
	public ArticleDetail getArticleDetail(int id) {
		Connection conn = pool.getConnection();
		String sql = "select ar.id,w.name,ca.name,ar.title,ar.content,ar.time,ar.baseUrl from isd_article ar,isd_web w,isd_category ca where ar.web=w.id and (ar.category=ca.id and ar.web=ca.web) and ar.id = ?";
		PreparedStatement ptmt = null;

		try {
			ptmt = conn.prepareStatement(sql);

			ptmt.setInt(1, id);

			ResultSet rs = ptmt.executeQuery();

			ArticleDetail ad = null;
			if (rs.next()) {
				ad = new ArticleDetail();
				ad.setId(rs.getInt("ar.id"));
				ad.setWeb_name(rs.getString("w.name"));
				ad.setCategory_name(rs.getString("ca.name"));
				ad.setTitle(rs.getString("ar.title"));
				ad.setContent(rs.getString("ar.content"));
				ad.setTime(rs.getString("ar.time"));
				ad.setBaseUrl(rs.getString("ar.baseUrl"));
			}

			return ad;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * 查找文章列表
	 * 
	 * 各个参数取默认值时sql不存在。
	 * 
	 * @param orderEle
	 *            用来进行排序的元素
	 * @param keyword
	 *            ”“
	 * @param sourceId
	 *            0
	 * @param type
	 *            type不限则是0
	 * @param pictureNumber
	 *            0
	 * @param date
	 *            0
	 * @return
	 */
	public List<ArticleBrief> searchArticles(long orderEle, String keyword, int []sourceId, int type, int pictureNumber,
			int date) {
		if(sourceId==null )
			{sourceId=new int[1];
			sourceId[0]=0;}
		
		String tempWeb="and( ar.web=? ";
		for(int i=1;i<sourceId.length;i++){
			tempWeb+="or ar.web=? ";
		}
		tempWeb+=")";
		String sql = "select ar.id,ar.title,ar.content,ar.imgs,ar.baseUrl,ar.time,w.name from isd_article ar,isd_web w where "
				+ "w.id = ar.web "
				+ (orderEle == 0 ? "and ar.time!=?" : " and ar.time<? ")
				+ " and ((ar.title like ?) or (ar.content like ?)) "
				+(type == 0 ? "and ar.atype!=? " : " and ar.atype=? ")
				+ (sourceId [0]== 0 ? "and ar.web!=?" : tempWeb)
				// + (type == 0 ? "ar.type!=?" : " and ar.type=?")
				+ (pictureNumber == 0 ? " " : " and " + convertPicIdtoSql(pictureNumber, "ar.imgs"))
				+ (date == 0 ? "" : " and " + convertTimetoSql(date, "ar.time")) + " order by ar.time DESC limit 16";

		System.out.println("orderEle:" + orderEle);
		System.out.println("keyword:" + keyword);
		System.out.println("sourceId:" + sourceId[0]);
		System.out.println("type:" + type);
		System.out.println("pictureNumber:" + pictureNumber);
		System.out.println("date:" + date);
		System.out.println("atype"+type);
		Connection conn = pool.getConnection();
		PreparedStatement ptmt = null;

		System.out.println("searchArticles-sql:" + sql);
		List<ArticleBrief> list = new ArrayList<>();
		try {
			ptmt = conn.prepareStatement(sql);

			ptmt.setLong(1, orderEle);
			ptmt.setString(2, "%" + keyword + "%");
			ptmt.setString(3, "%" + keyword + "%");
			ptmt.setInt(4, type);
			for(int i=0;i<sourceId.length;i++)
			ptmt.setInt(i+5, sourceId[i]);

			ResultSet rs = ptmt.executeQuery();
			
			while (rs.next()) {
				ArticleBrief ab = new ArticleBrief();
				ab.setId(rs.getInt("ar.id"));
				ab.setBaseUrl(rs.getString("ar.baseUrl"));
				ab.setContent(rs.getString("ar.content"));
				ab.setInfo(rs.getString("w.name") + "|" + rs.getString("ar.time"));
				ab.setTitle(rs.getString("ar.title"));
				String imgstr = rs.getString("ar.imgs");
				System.out.println(rs.getString("ar.content"));
				if (imgstr != null && !"".equals(imgstr)) {
					String[] imgs = imgstr.trim().split(";");
					Set<String> set = new HashSet<>();
					for (String img : imgs) {
						set.add(img);
					}
					ab.setImg(set);
				}
				list.add(ab);
			}
			System.out.println("listlength"+list.size());
			rs.close();
			ptmt.close();
			conn.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return null;
	}

	/******* 辅助方法 **********************************/
	/**
	 * 辅助将传过来的pictureNumber转化为sql片段
	 * 
	 * @param pictureNumber
	 *            传过来的id
	 * @param imgsStr
	 *            imgs的变量名，例如需要加上表名的 isd_article.imgs 等
	 * @return 对应的sql片段
	 */
	private String convertPicIdtoSql(int pictureNumber, String imgsStr) {

		switch (pictureNumber) {
		case 1:
			return " " + imgsStr + "!=0 ";// 1时为不限，任意返回全包含的sql
		case 2:
			return " (length(" + imgsStr + ")-length(replace(" + imgsStr + ",';',''))=1)"; // 1张图片
		case 3:
			return "(length(" + imgsStr + ")-length(replace(" + imgsStr + ",';',''))>1)";// 多张图片
		case 4:
			return " (" + imgsStr + " ='')";// 没有图片
		default:
			return " " + imgsStr + "!=0 "; // 默认返回不限
		}

	}

	/**
	 * 辅助将传过来的date转化为sql片段
	 * 
	 * @param timeId
	 * @param timeStr
	 * @return
	 */
	private String convertTimetoSql(int timeId, String timeStr) {

		long weekSeconds = 7 * 24 * 60 * 60;
		long monthSeconds = 31 * 24 * 60 * 60;
		switch (timeId) {
		case 1:
			return "( " + System.currentTimeMillis() / 1000 + "-" + timeStr + " < " + weekSeconds + ")";// 1为1周之内
		case 2:
			return "( " + System.currentTimeMillis() / 1000 + "-" + timeStr + " < " + monthSeconds + ")"; // 2为1个月内
		case 3:
			return "( " + System.currentTimeMillis() / 1000 + "-" + timeStr + " > " + monthSeconds + ")";// 3为更早
		default:
			return "(" + timeStr + "!=0 )"; // 默认返回不限
		}

	}

	/******* 尝试过的失败的方法 **********************************/

	/**
	 * 保存，去重，返回保存数据的id,失效用不了
	 * 
	 * @param articles
	 * @return
	 */
	public boolean saveBatchAndReturnId(List<Article> articles) {
		Connection conn = pool.getConnection();
		String sql = "insert into isd_article(web,category,title,content,time,baseUrl,imgs,atype) values(?,?,?,?,?,?,?,?) on duplicate key update web=?,category=?,content=?,time=?,baseUrl=?,imgs=?,atype=? ";
		PreparedStatement ptmt = null;

		try {
			conn.setAutoCommit(false);
			String[] columnNames = new String[] { "id" };
			ptmt = conn.prepareStatement(sql, columnNames);

			for (Article article : articles) {
				ptmt.setInt(1, article.getWeb());
				ptmt.setInt(2, article.getCategory());
				ptmt.setString(3, article.getTitle());
				ptmt.setString(4, article.getContent());
				ptmt.setString(5, article.getTime());
				ptmt.setString(6, article.getBaseUrl());
				ptmt.setString(7, article.getImgs());
				ptmt.setInt(8, article.getAtype());
				
				ptmt.setInt(9, article.getWeb());
				ptmt.setInt(10, article.getCategory());
				ptmt.setString(11, article.getContent());
				ptmt.setString(12, article.getTime());
				ptmt.setString(13, article.getBaseUrl());
				ptmt.setString(14, article.getImgs());
				ptmt.setInt(15, article.getAtype());
				ptmt.addBatch();
			}

			ptmt.executeBatch();
			ResultSet rs = ptmt.getGeneratedKeys();
			int index = 0;
			System.out.println("rs rows :" + rs.getRow());
			while (rs.next()) {
				int id = rs.getInt(1);
				articles.get(index).setId(id);
				System.out.println("id is " + id + ",baseUrl is " + articles.get(index).getBaseUrl());
				index++;
			}
			ptmt.close();
			conn.commit();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
			return false;
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

}